using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Text;
using System.Data.OleDb;
using System.Net;
using System.IO;
namespace NLBS.Data
{
    public partial class SqlHelper
    {
        public static int ExecuteNonQuery(string query, Database db)
        {
            DbCommand cmd;
            if (query.StartsWith("INSERT") | query.StartsWith("insert") | query.StartsWith("UPDATE") | query.StartsWith("update") | query.StartsWith("DELETE") | query.StartsWith("delete"))
            {
                cmd = db.GetSqlStringCommand(query);
            }
            else
            {
                cmd = db.GetStoredProcCommand(query);
            }

            int retval;
            try
            {
                retval = db.ExecuteNonQuery(cmd);
            }
            catch (Exception exp)
            {
                throw exp;
            }
            return retval;
        }

        public static int ExecuteNonQuery(string query, Database db, params DbParameter[] parameters)
        {
            DbCommand cmd;
            if (query.StartsWith("INSERT") | query.StartsWith("insert") | query.StartsWith("UPDATE") | query.StartsWith("update") | query.StartsWith("DELETE") | query.StartsWith("delete"))
            {
                cmd = db.GetSqlStringCommand(query);
            }
            else
            {
                cmd = db.GetStoredProcCommand(query);
            }
            cmd.Parameters.AddRange(parameters);
            int retval = db.ExecuteNonQuery(cmd);
            return retval;
        }

        public static object ExecuteScalar(string query, Database db)
        {
            DbCommand cmd;
            if (query.StartsWith("SELECT") | query.StartsWith("select"))
            {
                cmd = db.GetSqlStringCommand(query);
            }
            else
            {
                cmd = db.GetStoredProcCommand(query);
            }

            object retval;
            try
            {
                retval = db.ExecuteScalar(cmd);
            }
            catch (Exception exp)
            {
                throw exp;
            }
            return retval;
        }

        public static object ExecuteScalar(string query, Database db, params DbParameter[] parameters)
        {
            DbCommand cmd;
            if (query.StartsWith("SELECT") | query.StartsWith("select"))
            {
                cmd = db.GetSqlStringCommand(query);
            }
            else
            {
                cmd = db.GetStoredProcCommand(query);
            }
            cmd.Parameters.AddRange(parameters);
            object retval;
            try
            {
                retval = db.ExecuteScalar(cmd);
            }
            catch (Exception exp)
            {
                throw exp;
            }
            return retval;
        }

        public static IDataReader ExecuteReader(string query, Database db)
        {
            DbCommand cmd;
            if (query.StartsWith("SELECT") | query.StartsWith("select"))
            {
                cmd = db.GetSqlStringCommand(query);
            }
            else
            {
                cmd = db.GetStoredProcCommand(query);
            }

            
            IDataReader dr;
            try
            {
                dr = db.ExecuteReader(cmd);
            }
            catch (Exception exp)
            {
                throw exp;
            }
            return dr;

        }

        public static IDataReader ExecuteReader(string query, Database db, params DbParameter[] parameters)
        {
            DbCommand cmd;
            if (query.StartsWith("SELECT") | query.StartsWith("select"))
            {
                cmd = db.GetSqlStringCommand(query);
            }
            else
            {
                cmd = db.GetStoredProcCommand(query);
            }

            
            cmd.Parameters.AddRange(parameters);
            IDataReader dr;
            try
            {
                dr = db.ExecuteReader(cmd);
            }
            catch (Exception exp)
            {
                throw exp;
            }
            return dr;
        }

        public DataSet ExecuteDataSet(string query, Database db)
        {
            DbCommand cmd;
            if (query.StartsWith("SELECT") | query.StartsWith("select"))
            {
                cmd = db.GetSqlStringCommand(query);
            }
            else
            {
                cmd = db.GetStoredProcCommand(query);
            }
            return db.ExecuteDataSet(cmd);
        }

        public DataSet ExecuteDataSet(string query, Database db, params SqlParameter[] parameters)
        {
            DbCommand cmd;
            if (query.StartsWith("SELECT") | query.StartsWith("select"))
            {
                cmd = db.GetSqlStringCommand(query);
            }
            else
            {
                cmd = db.GetStoredProcCommand(query);
            }
            cmd.Parameters.AddRange(parameters);

            return db.ExecuteDataSet(cmd);
        }

        public static void RunSQL(string fileUrl, string connectionString, int timeout)
        {
            SqlConnection conn = null;
            try
            {
                //this.Response.Write(String.Format("Opening url {0}<BR>", fileUrl));

                // read file
                WebRequest request = WebRequest.Create(fileUrl);
                using (StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream()))
                {
                    //this.Response.Write("Connecting to SQL Server database...<BR>");

                    // Create new connection to database
                    conn = new SqlConnection(connectionString);

                    conn.Open();

                    while (!sr.EndOfStream)
                    {
                        StringBuilder sb = new StringBuilder();
                        SqlCommand cmd = conn.CreateCommand();

                        while (!sr.EndOfStream)
                        {
                            string s = sr.ReadLine();
                            if (s != null && s.ToUpper().Trim().Equals("GO"))
                            {
                                break;
                            }

                            sb.AppendLine(s);
                        }

                        // Execute T-SQL against the target database
                        cmd.CommandText = sb.ToString();
                        cmd.CommandTimeout = timeout;

                        cmd.ExecuteNonQuery();
                    }

                }
                //this.Response.Write("T-SQL file executed successfully");
            }
            catch (Exception ex)
            {
                //this.Response.Write(String.Format("An error occured: {0}", ex.ToString()));
                throw ex;
            }
            finally
            {
                // Close out the connection
                //
                if (conn != null)
                {
                    try
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                    catch (Exception e)
                    {
                        //this.Response.Write(String.Format(@"Could not close the connection.  Error was {0}", e.ToString()));
                        throw e;
                    }
                }
            }
        }

        public static void RunAccessSQL(string fileUrl, string connectionString, int timeout)
        {
            OleDbConnection conn = null;
            try
            {
                //this.Response.Write(String.Format("Opening url {0}<BR>", fileUrl));

                // read file
                WebRequest request = WebRequest.Create(fileUrl);
                using (StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream()))
                {
                    //this.Response.Write("Connecting to SQL Server database...<BR>");

                    // Create new connection to database
                    conn = new OleDbConnection(connectionString);

                    conn.Open();

                    while (!sr.EndOfStream)
                    {
                        StringBuilder sb = new StringBuilder();
                        OleDbCommand cmd = conn.CreateCommand();

                        while (!sr.EndOfStream)
                        {
                            string s = sr.ReadLine();
                            if (s != null && s.ToUpper().Trim().Equals("GO"))
                            {
                                break;
                            }

                            sb.AppendLine(s);
                        }

                        // Execute T-SQL against the target database
                        cmd.CommandText = sb.ToString();
                        cmd.CommandTimeout = timeout;

                        cmd.ExecuteNonQuery();
                    }

                }
                //this.Response.Write("T-SQL file executed successfully");
            }
            catch (Exception ex)
            {
                //this.Response.Write(String.Format("An error occured: {0}", ex.ToString()));
                throw ex;
            }
            finally
            {
                // Close out the connection
                //
                if (conn != null)
                {
                    try
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                    catch (Exception e)
                    {
                        //this.Response.Write(String.Format(@"Could not close the connection.  Error was {0}", e.ToString()));
                        throw e;
                    }
                }
            }
        }
    }
}